#!/bin/bash
APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else 
   echo "请传入日期参数"
   exit
fi 

dim_province_full="
insert overwrite table ${APP}.dim_province_full partition(dt='$do_date')
select 
    id,
    name,
    region_id,
    area_code,
    iso_code,
    iso_3166_2
from ${APP}.ods_base_province_full
where dt = '$do_date';
"

dim_user_zip="
insert overwrite table ${APP}.dim_user_zip partition (dt='9999-12-31')
select 
data.id,
data.login_name,
data.nick_name,
md5(data.real_name),
md5(if(data.phone_num regexp '^(13[0-9]|14[01456879]|15[0-35-9]|16[2567]|17[0-8]|18[0-9]|19[0-35-9])\\d{8}$',
data.phone_num,null)),
md5(if(data.email regexp '^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\\.[a-zA-Z0-9_-]+)+$',data.email,null)),
data.head_img,
data.user_level,
data.birthday,
data.gender,
data.create_time,
data.operate_time,
data.status,
'$do_date' start_date,
'9999-12-31' end_date
from ${APP}.ods_user_info_inc
where dt = '$do_date' and type = 'bootstrap-insert';
"

dim_course_full="
with
course as 
(
select
id,
course_name,
course_slogan,
course_cover_url,
subject_id,
teacher,
publisher_id,
chapter_num,
origin_price,
reduce_amount,
actual_price,
course_introduce
from ${APP}.ods_course_info_full
where dt = '$do_date'
),
subject as 
(
select
id,
subject_name,
category_id
from ${APP}.ods_base_subject_info_full 
where dt = '$do_date'
),
category as 
(
select
id,
category_name,
create_time,
update_time,
deleted
from ${APP}.ods_base_category_info_full 
where dt = '$do_date'
)
insert overwrite table ${APP}.dim_course_full partition(dt='$do_date')
select
course.id,
course.course_name,
course.course_slogan,
course.course_cover_url,
course.subject_id,
subject.subject_name,
subject.category_id,
category.category_name,
course.teacher,
course.publisher_id,
course.chapter_num,
course.origin_price,
course.reduce_amount,
course.actual_price,
course.course_introduce,
category.create_time,
category.update_time,
category.deleted
from course
left join subject on course.subject_id = subject.id
left join category on subject.category_id = category.id; 
"

dim_test_paper_full="
insert overwrite table ${APP}.dim_test_paper_full partition(dt='$do_date')
select
id,
paper_title,
course_id,
create_time,
update_time,
publisher_id,
deleted
from ${APP}.ods_test_paper_full
where dt = '$do_date';
"

dim_chapter_full="
insert overwrite table ${APP}.dim_chapter_full partition(dt='$do_date')
select
t1.id,
t1.chapter_name,
t1.course_id,
t1.video_id,
t2.video_name,
t2.during_sec,
t2.publisher_id,
t1.is_free,
t2.create_time,
t2.update_time,
t2.deleted
from
(
    (select
id,
chapter_name,
course_id,
video_id,
publisher_id,
is_free,
create_time,
update_time,
deleted      
from ${APP}.ods_chapter_info_full 
where dt = '$do_date') t1 
left join 
(
     select
data.id,
data.video_name,
data.during_sec,
data.video_status,
data.video_size,
data.video_url,
data.video_source_id,
data.version_id,
data.chapter_id,
data.course_id,
data.publisher_id,
data.create_time,
data.update_time,
data.deleted
     from  ${APP}.ods_video_info_inc
     where  dt = '$do_date' 
     and type = 'bootstrap-insert'
) t2 
on t1.video_id =  t2.id
);
"



case $1 in
"dim_chapter_full")
    hive -e "$dim_chapter_full"
;;
"dim_province_full")
    hive -e "$dim_province_full"
;;
"dim_course_full")
    hive -e "$dim_course_full"
;;
"dim_test_paper_full")
    hive -e "$dim_test_paper_full"
;;
"dim_user_zip")
    hive -e "$dim_user_zip"
;;
"all")
    hive -e "$dim_chapter_full$dim_province_full$dim_course_full$dim_test_paper_full$dim_user_zip"
;;
esac

